package com.greateamd.timetableasy.server.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.greateamd.timetableasy.client.domain.AuthType;
import com.greateamd.timetableasy.domain.Event;
import com.greateamd.timetableasy.domain.Promotion;
import com.greateamd.timetableasy.domain.User;
import com.greateamd.timetableasy.domain.exceptions.DBServerSideException;
import com.greateamd.timetableasy.domain.exceptions.IllegalEntityStateException;
import com.greateamd.timetableasy.server.ServerUtil;
import com.greateamd.timetableasy.server.dao.PromotionDAO;
import com.greateamd.timetableasy.server.dao.util.AbstractHibernateDAO;



@Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.READ_COMMITTED, readOnly = false)	
@Repository("promotionDAO")
public class PromotionDAOImpl extends AbstractHibernateDAO<Promotion,Integer> implements PromotionDAO {

	public PromotionDAOImpl() {
		super(new Promotion());
	}

	@Override
	public List<Event> getPlanning(final Promotion promotion, final Date from, final Date to) throws DBServerSideException, IllegalEntityStateException {
		logger.debug("Get "+promotion.getId()+"'s promotion planning from db");
		HibernateCallback callback = new HibernateCallback() {

			public Object doInHibernate(Session session) throws HibernateException,SQLException {
				final List<Event> events = new ArrayList<Event>();
				
				//get promotion events
				String hql = "SELECT distinct(event) " +
				 "FROM Promotion promotion " +
				 "JOIN promotion.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE promotion.id = :promotionId " +
				 "ORDER BY event.startDate";
				Query query = session.createQuery(hql);
				query.setParameter("promotionId", promotion.getId());
				query.setDate("from", from);
				query.setDate("to", to);
				List list = query.list();
				events.addAll(list);
				
				//get promotion.campus events
				hql = "SELECT distinct(event) " +
				 "FROM Promotion promotion " +
				 "JOIN promotion.campus campus JOIN campus.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE promotion.id = :promotionId " +
				 "ORDER BY event.startDate";
				query = session.createQuery(hql);
				query.setParameter("promotionId", promotion.getId());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);
				
				//get promotion.campus.university events
				hql = "SELECT distinct(event) " +
				 "FROM Promotion promotion " +
				 "JOIN promotion.campus.university university JOIN university.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE promotion.id = :promotionId " +
				 "ORDER BY event.startDate";
				query = session.createQuery(hql);
				query.setParameter("promotionId", promotion.getId());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);

				//get promotion.courses events
				hql = "SELECT distinct(event) " +
				 "FROM Promotion promotion " +
				 "JOIN promotion.courses course JOIN course.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE promotion.id = :promotionId " +
				 "ORDER BY event.startDate";
				query = session.createQuery(hql);
				query.setParameter("promotionId", promotion.getId());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);
				
				//get promotion.study_period events
				hql = "SELECT distinct(event) " +
				 "FROM Promotion promotion " +
				 "JOIN promotion.studyPeriods sp JOIN sp.events event " +
				 	"WITH event.startDate >= :from AND event.startDate <= :to "+
			 	 "WHERE promotion.id = :promotionId " +
				 "ORDER BY event.startDate";
				query = session.createQuery(hql);
				query.setParameter("promotionId", promotion.getId());
				query.setDate("from", from);
				query.setDate("to", to);
				list = query.list();
				events.addAll(list);
				
				ServerUtil.sortStartDateList(events);
				
				return events;
	        }
	    };
	    return (List) getHibernateTemplate().execute(callback);
	}

	@Override
	public List<Promotion> getAllOfCampusManager() throws HibernateException,SQLException {
		logger.debug("Get all campus manager's promotion from db");
		HibernateCallback callback = new HibernateCallback() {

			public List<Promotion> doInHibernate(Session session) throws HibernateException,SQLException {
				//
				final User loggedUser = getHibernateTemplate().execute(new HibernateCallback<User>() {
					@Override
					public User doInHibernate(Session session) throws HibernateException, SQLException {
						final String username = SecurityContextHolder.getContext().getAuthentication().getName();
						return (User) session.get(User.class, username);
					}});
				
				//get logged user managed campus
				String hql = "SELECT distinct(promotion) " +
				 "FROM Promotion promotion " +
				 "JOIN promotion.campus campus JOIN campus.managers manager " +
				 	"WITH manager.username = :username ";
				Query query = session.createQuery(hql);
				query.setParameter("username", loggedUser.getUsername());
				List list = query.list();
				return list;
	        }
	    };
	    return (List) getHibernateTemplate().execute(callback);
	}
}
